const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 * 平板拣货系统后台
 */

/**
 * 
 * 拣货系统登录 
 */
const pickGoodsSystemLogin = async (ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT DLMC,BM,MC FROM BM_YG WHERE BM IN (SELECT BM FROM YG_QYFP WHERE status = 'T') AND DLMC = '${body.username}' AND MM = '${body.password}'`;
    // let selectSql = `SELECT username,id FROM JHY WHERE username = '${body.username}' AND password = '${body.password}'`;
    let result = await sql.query(selectSql);
    if (result.rowsAffected[0] === 1) {
        ctx.body = { success: true, result: result.recordset[0] }
    } else {
        ctx.body = { success: false }
    }
}

// 获取用户权限状态
const getPickGoodsUserStatus = async (ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT area  FROM YG_QYFP WHERE BM = '${body.userId}'`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset[0].area }
}

/**
 * 
 * 获取接单信息 
 */
const getPickGoodsOrder = async (ctx, next) => {
    try {
        const body = ctx.request.body;
        let distributionInfo = await sql.query(`SELECT * FROM YG_QYFP WHERE BM = '${body.userId}'`);
        let distributionInfoResult = distributionInfo.recordset;
        let actionSql = `SELECT X.ID,X.RQ,X.DWBM,X.DWMC,X.JSFSMC,X.ZJE,B.DH FROM XSD_ZB X  LEFT JOIN BM_WLDW B ON X.DWBM = B.BM 
    WHERE X.SH = 'T' AND X.ID_DHD <> '' AND X.ID NOT IN (SELECT ID FROM FPDD) AND X.ID IN (SELECT T1.ID FROM (SELECT ID,YPBM FROM XSD_MX 
        GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)>1))`;

        // (SELECT T2.ID FROM  (SELECT COUNT(T1.ID) AS PX,T1.ID FROM ( SELECT ID FROM XSD_MX GROUP BY ID,YPBM) T1 GROUP BY T1.ID) T2 WHERE T2.PX >1 )
        // 分配拣货区域且状态是启用
        if ((0 in distributionInfoResult) && distributionInfoResult[0].status === 'T') {

            //     if(distributionInfoResult[0].area === 1){
            //           // 分配的是单订单阴凉库
            //         actionSql += ` AND X.ID IN (SELECT T1.ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX  GROUP BY ID ) T1 LEFT JOIN (SELECT HWBH,ID FROM XSD_MX ) T2 ON T1.ID = T2.ID  WHERE T1.PX = 1 AND T2.HWBH LIKE '__L%') `;
            //     } else if(distributionInfoResult[0].area === 2){
            //           // 分配的是单订单常温库
            //         actionSql += ` AND X.ID IN (SELECT T1.ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX  GROUP BY ID ) T1 LEFT JOIN (SELECT HWBH,ID FROM XSD_MX ) T2 ON T1.ID = T2.ID  WHERE T1.PX = 1 AND T2.HWBH LIKE '__W%') `;
            //     }else if(distributionInfoResult[0].area === 3){ 
            //           // 分配的是多订单所有库
            //         actionSql += ` AND X.ID IN (SELECT ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX GROUP BY ID ) T1 WHERE T1.PX > 1) `;
            //     }else if(distributionInfoResult[0].area === 4){
            //         // 分配的是单订单所有库
            //       actionSql += ` AND X.ID IN (SELECT ID FROM (SELECT COUNT(ID) AS PX,ID FROM XSD_MX GROUP BY ID ) T1 WHERE T1.PX = 1) `;
            //   }


            if (distributionInfoResult[0].area === 1) {
                // 分配的是阴凉库
                actionSql += ` AND X.ID IN (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__L%') AND X.ID <>ALL (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__W%') `;
            } else if (distributionInfoResult[0].area === 2) {
                // 分配的是常温库
                actionSql += ` AND X.ID IN (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__W%') AND X.ID <>ALL (SELECT ID FROM XSD_MX WHERE HWBH LIKE '__L%') `;
            }

        }
        actionSql += ` AND X.RQ >='${baseFn.startTime}'  ORDER BY CONVERT(varchar(10), X.RQ, 120 ) ,CASE X.JSFSBM WHEN '15' THEN 1  WHEN '11' THEN 2  WHEN '13' THEN 3  WHEN '12' THEN 4 END `;
        // actionSql += ` AND X.RQ >='${baseFn.startTime}'  ORDER BY X.RQ DESC `;

        let result = await sql.query(actionSql);
        console.log(actionSql);
        if (result.rowsAffected[0] > 0) {
            let selectFpdd = await sql.query(`SELECT * FROM FPDD WHERE ID = '${result.recordset[0].ID}'`);
            if (selectFpdd.rowsAffected[0] > 0) {
                ctx.body = { success: 1 }
                return;
            }
            let insertGotOrderSql = `INSERT INTO FPDD VALUES ('${result.recordset[0].ID}')`;
            sql.query(insertGotOrderSql)
            let selectShopSql = `SELECT YPBM,YPMC,YPGG,SL,SCPH ,SCCJ FROM XSD_MX WHERE ID = '${result.recordset[0].ID}'`;
            let allShop = await sql.query(selectShopSql);
            let shopCountSql = `SELECT SUM(SL) AS shopCount FROM XSD_MX WHERE ID = '${result.recordset[0].ID}' GROUP BY ID`;
            let shopCount = await sql.query(shopCountSql);

            let time = setTimeout(async () => {
                let isGetOrderSql = `SELECT COUNT(*) as num FROM YHDD WHERE id = '${result.recordset[0].ID}'`;
                let isGetOrder = await sql.query(isGetOrderSql);
                if (isGetOrder.recordset[0].num == 0) {
                    let deleteSql = `DELETE FROM FPDD WHERE ID = '${result.recordset[0].ID}'`
                    sql.query(deleteSql);
                }
                clearTimeout(time);
            }, 300000)

            ctx.body = { success: true, result: result.recordset[0], allShop: allShop.recordset, shopCount: shopCount.recordset[0].shopCount }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        // 接了重复订单
        ctx.body = { success: 1 }
    }
}

/**
 * 获得订单
 *  */
const havePickGoodsOrder = async (ctx, next) => {
    const body = ctx.request.body;
    console.log('body', body)
    let regCarNum = /^[A-Z]\d{3}$/g;
    if (regCarNum.test(body.carNum)) {
        let isExistSql = `SELECT COUNT(id) AS num FROM YHDD WHERE id = '${body.orderId}'`;
        let isExistResult = await sql.query(isExistSql);

        if (isExistResult.recordset[0].num == 0) {
            let selectShopSql = `SELECT YPBM,SCPH FROM XSD_MX WHERE ID = '${body.orderId}'`;
            let allShop = await sql.query(selectShopSql);
            let insertSql = `INSERT INTO YHDD (userid,id,carid,YPBM,JHZT,SCPH,FHZT,SFYC,JHRQ,FHRQ,JHWZ) `;
            allShop.recordset.forEach((item, ind) => {
                if (ind == allShop.recordset.length - 1) {
                    insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','','${body.JHWZ}' `
                } else {
                    insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','','${body.JHWZ}' UNION `
                }
            });
            let result = await sql.query(insertSql);
            if (result.rowsAffected[0] > 0) {
                ctx.body = { success: true }
            } else {
                ctx.body = { success: false }
            }
        } else {
            ctx.body = { success: false }
        }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 获得接单员的所有已接订单
 */
const getPickGoodsUserAllOrder = async (ctx, next) => {
    const body = ctx.request.body;
    // let selectSql = `SELECT t1.id,t1.carid,X.RQ,X.DWBM,X.DWMC,X.JSFSMC,X.ZJE,B.DH FROM (SELECT id, carid  FROM YHDD WHERE userid = ${body.userId} AND JHZT = 'F' AND id <>ALL (SELECT id FROM YHDD WHERE SFYC = 'T') GROUP BY id,carid) as t1 LEFT JOIN (SELECT COUNT(ID) AS SPSL,ID FROM XSD_MX GROUP BY ID) T2 ON t1.id = T2.ID  LEFT JOIN XSD_ZB X ON t1.id = X.ID LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE T2.SPSL > 1`;
    let selectSql = `SELECT t1.id,t1.carid,X.RQ,X.DWBM,X.DWMC,X.JSFSMC,X.ZJE,B.DH FROM (SELECT id, carid  FROM YHDD WHERE userid = ${body.userId} 
        AND JHZT = 'F' AND id <>ALL (SELECT id FROM YHDD WHERE SFYC = 'T')  GROUP BY id,carid) as t1   LEFT JOIN XSD_ZB X ON t1.id = X.ID 
    LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE t1.id in (SELECT T2.ID FROM (SELECT ID,YPBM FROM YHDD  GROUP BY ID,YPBM)T2 
    GROUP BY T2.ID  HAVING(COUNT(T2.YPBM)>1))`;
    let result = await sql.query(selectSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset }
    } else {
        ctx.body = { success: false }
    }
}


/**
 * 获得接单员的所有已接订单的商品信息
 */
const getPickGoodsUserAllOrderShop = async (ctx, next) => {
    const body = ctx.request.body;
    // let selectSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH  FROM 
    // YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH 
    // WHERE Y.userid = '${body.userId}' AND Y.JHZT = 'F' AND Y.SFYC = 'F' GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,
    // Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH`;
    // let selectAllSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHRQ  FROM 
    // YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH 
    // WHERE Y.userid = '${body.userId}' AND Y.FHZT = 'F' AND Y.SFYC = 'F' GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,
    // Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHRQ `;

    let selectSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.PZWH,X.JXMC,B.CCTJ,X.HWBH,Y.carid,H.FQBH,KC.KCSL,Y.JHWZ FROM 
    YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH  LEFT JOIN BM_YP B ON
     Y.YPBM = B.BM  LEFT JOIN (SELECT YPBM,SUM(KCSL) AS KCSL FROM KC_KCDTB WHERE BMBM = '07' GROUP BY YPBM) KC ON KC.YPBM = Y.YPBM WHERE Y.ID IN (
		SELECT T2.ID FROM (SELECT ID,YPBM FROM YHDD   GROUP BY ID,YPBM)T2 
    GROUP BY T2.ID  HAVING(COUNT(T2.YPBM)>1)
		) AND Y.userid = ${body.userId} 
        AND Y.JHZT = 'F' AND Y.SFYC = 'F'   GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,
    Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,X.PZWH,X.JXMC,B.CCTJ,KC.KCSL,Y.JHWZ`;
    let selectAllSql = `SELECT Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,SUM(X.SL) AS SL,Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHWZ  FROM 
    YHDD Y LEFT JOIN  XSD_MX X ON Y.id = X.id AND Y.YPBM = X.YPBM AND Y.SCPH = X.SCPH LEFT JOIN HWBH H ON X.HWBH = H.HWBH 
    WHERE Y.ID IN (
		SELECT T2.ID FROM (SELECT ID,YPBM FROM YHDD   GROUP BY ID,YPBM)T2 
    GROUP BY T2.ID  HAVING(COUNT(T2.YPBM)>1)
		) AND Y.userid = ${body.userId} 
        AND Y.FHZT = 'F' AND Y.SFYC = 'F'  GROUP BY Y.ID,Y.YPBM,X.JLDW,X.YPMC,X.YPGG,X.SCCJ,X.SL,
    Y.SCPH,X.YXQZ,X.HWBH,Y.carid,H.FQBH,Y.JHZT,Y.JHWZ`;
    console.log(selectSql);
    let result = await sql.query(selectSql);
    let resultAll = await sql.query(selectAllSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset, resultAll: resultAll.recordset }
    } else {
        ctx.body = { success: false }
    }
}


/**
 * 开始拣货
 */
const pickGoods = async (ctx, next) => {
    const body = ctx.request.body;
    console.log('body', body)
    // let nowTime  =  new Date();
    let updateSql = `UPDATE YHDD SET JHZT = 'T',JHRQ = '${baseFn.dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}' WHERE userid = '${body.userId}' AND id = '${body.orderId}' AND YPBM = '${body.ypbm}' AND SCPH = '${body.scph}'`;
    // let deleteSql = `DELETE FROM YHDD WHERE userid = ${body.userId} AND id = '${body.orderId}' AND HWBH = '${body.hwbh}'`;
    // let selectSql = `SELECT X.ID,X.YPBM,X.YPMC,X.YPGG,X.SCCJ,X.SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.HWQH FROM  XSD_MX X LEFT JOIN HWBH H ON X.HWBH = H.HWBH LEFT JOIN YHDD Y ON X.HWBH = Y.HWBH  WHERE X.ID IN (SELECT id FROM YHDD WHERE userid = ${body.userId}) AND Y.JHZT = 'F'`;
    // let result = await sql.query(deleteSql);
    let result = await sql.query(updateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}
/**
 * 开始拣货和跳过订单
 */
const dealOrder = async (ctx, next) => {
    const { userId, ypbm, reason, person, list } = ctx.request.body;
    console.log('list',list)
    let goodList = list.filter(item => item.checked)
    let badList = list.filter(item => !item.checked)
    let actionSql = []
    if (goodList.length > 0) {
        goodList.forEach(item => {
            actionSql.push(`UPDATE YHDD SET JHZT = 'T',JHRQ = '${baseFn.dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}' WHERE userid = '${userId}' AND id = '${item.orderId}' AND YPBM = '${ypbm}' AND SCPH = '${item.scph}'`)
        })
    }
    if (badList.length > 0) {
        badList.forEach(item => {
            actionSql.push(`INSERT INTO JHCY VALUES('${item.orderId}','${person}','${reason}','${ypbm}','${item.scph}','${baseFn.dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}','')`)
            actionSql.push(`UPDATE YHDD SET SFYC = 'T' WHERE id ='${item.orderId}' AND YPBM ='${ypbm}' AND SCPH ='${item.scph}' `)
        })

    }
    let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSql.join(' ')} Commit Tran `;
    let result = await sql.query(lastSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}



/**
 * 改变异常订单的状态
 */
const setPickGoodsAbnormalOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let insertSql = `INSERT INTO JHCY VALUES('${body.shopInfo.ID}','${body.person}','${body.reason}','${body.shopInfo.YPBM}','${body.shopInfo.SCPH}','${baseFn.dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}')`;
    let insertResult = await sql.query(insertSql);
    let updateSql = `UPDATE YHDD SET SFYC = 'T' WHERE userid = '${body.userId}' AND id = '${body.orderId}' AND YPBM = '${body.ypbm}' AND SCPH = '${body.scph}'`;
    // // let deleteSql = `DELETE FROM YHDD WHERE userid = ${body.userId} AND id = '${body.orderId}' AND HWBH = '${body.hwbh}'`;
    // // let selectSql = `SELECT X.ID,X.YPBM,X.YPMC,X.YPGG,X.SCCJ,X.SL,X.SCPH,X.YXQZ,X.HWBH,Y.carid,H.HWQH FROM  XSD_MX X LEFT JOIN HWBH H ON X.HWBH = H.HWBH LEFT JOIN YHDD Y ON X.HWBH = Y.HWBH  WHERE X.ID IN (SELECT id FROM YHDD WHERE userid = ${body.userId}) AND Y.JHZT = 'F'`;
    // // let result = await sql.query(deleteSql);
    let result = await sql.query(updateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 
 * 获得可接订单的数量
 */
const getPickGoodOrderCount = async (ctx, next) => {
    // let selectSql = `SELECT COUNT(X.ID) AS total FROM XSD_ZB X LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE X.SH = 'T' AND X.ID_DHD <> '' AND X.ID <> ALL (SELECT ID FROM FPDD) AND X.ID IN 
    // (SELECT T2.ID FROM  (SELECT COUNT(T1.ID) AS PX,T1.ID FROM ( SELECT ID FROM XSD_MX GROUP BY ID,YPBM) T1 GROUP BY T1.ID) T2 WHERE T2.PX >1 ) AND X.RQ >='${baseFn.startTime}' `;
    let selectSql = `SELECT COUNT(X.ID) AS total FROM XSD_ZB X LEFT JOIN BM_WLDW B ON X.DWBM = B.BM WHERE X.SH = 'T' AND X.ID_DHD <> '' 
    AND X.ID <> ALL (SELECT ID FROM FPDD) AND X.ID IN (SELECT T1.ID FROM (SELECT ID,YPBM FROM XSD_MX GROUP BY ID,YPBM)T1 GROUP BY T1.ID  
    HAVING(COUNT(T1.YPBM)>1)) AND X.RQ >='${baseFn.startTime}'`;
    let result = await sql.query(selectSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: result.recordset[0].total }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 判断购物车是否被占用
 */
const isUsedCarNum = async (ctx, next) => {
    const body = ctx.request.body;
    let selectSql = `SELECT COUNT(carid) AS car FROM YHDD WHERE FHZT = 'F' AND carid = '${body.carNum}'`;
    let result = await sql.query(selectSql);
    if (result.recordset[0].car > 0) {
        ctx.body = { success: false }
    } else {
        ctx.body = { success: true }
    }
}



module.exports = {
    'POST /pickGoodsSystemLogin': pickGoodsSystemLogin,
    'POST /getPickGoodsOrder': getPickGoodsOrder,
    'POST /havePickGoodsOrder': havePickGoodsOrder,
    'POST /getPickGoodsUserAllOrder': getPickGoodsUserAllOrder,
    'POST /getPickGoodsUserAllOrderShop': getPickGoodsUserAllOrderShop,
    'POST /pickGoods': pickGoods,
    'POST /isUsedCarNum': isUsedCarNum,
    'POST /getPickGoodsUserStatus': getPickGoodsUserStatus,
    'GET /getPickGoodOrderCount': getPickGoodOrderCount,
    'POST /setPickGoodsAbnormalOrder': setPickGoodsAbnormalOrder,
    'POST /dealOrder': dealOrder,

}